# coding=utf-8
# update stock code

from wddb import initDb
from htmlUtil import downHTML,downFile
import os
from stockCommon import delPathFiles
import xlrd


def getShenzenStockCodes():
    refer="http://www.szse.cn/market/stock/list/index.html"
    savePath=os.getcwd()+os.sep+"temp"
    saveFile=savePath+os.sep+"sz.xlsx"
    if not os.path.exists(savePath):
        os.makedirs(savePath)
    stockCodeXlsURL="http://www.szse.cn/api/report/ShowReport?SHOWTYPE=xlsx&CATALOGID=1110&TABKEY=tab1&random=0.14202295689876854"
    downFile(stockCodeXlsURL,saveFile,refer)
    workbook = xlrd.open_workbook(saveFile)
    sheet=workbook.sheet_by_index(0)
    nrows=sheet.nrows
    rls=[]
    for idx in range(1,nrows):
        c=sheet.cell(idx,4).value
        s=sheet.cell(idx,5).value
        a={}
        a["code"]=c
        a["name"]=s
        a["prefix163"]=1
        #print(a)
        rls.append(a)
    delPathFiles(savePath)
    return rls

def getShanhaiStockCodes():
    refer="http://www.sse.com.cn/assortment/stock/list/share/"
    stockCodeXlsURL="http://query.sse.com.cn/security/stock/downloadStockListFile.do?csrcCode=&stockCode=&areaName=&stockType=1"
    csv=downHTML(stockCodeXlsURL,"gb2312",refer)
    #print(csv)
    colSpliterChar="|"
    lines=csv.splitlines()
    rls=[]
    for idx in range(1,len(lines)):
        row=lines[idx]
        row=row.replace(" 	","|")
        row=row.replace("	  ","|")
        row=row.replace(" ","")
        #print(row)
        cols=row.split(colSpliterChar)
        c=cols[2]
        s=cols[3]
        a={}
        a["code"]=c
        a["name"]=s
        a["prefix163"]=0
        #print(a)
        rls.append(a)
    return rls

def  cleanTable(con):
    cur = con.cursor()
    sql="delete from stock_code;"
    cur.execute(sql)

def saveStockCode(con,a):
    print(a)
    cur = con.cursor()
    sql='INSERT INTO stock_code (code,prefix163,name \
    ) VALUES (\'%s\',%s,\'%s\')' %(a["code"],a["prefix163"],a["name"])
    print(sql)
    cur.execute(sql)
    cur.close()
    
def updateStockCode():
    ls1=getShanhaiStockCodes()
    ls2=getShenzenStockCodes()
    con=initDb()
    try:
        cleanTable(con)
        for a in ls1:
            saveStockCode(con,a)
        for a in ls2:
            saveStockCode(con,a)
        con.commit()
    finally:
        con.close()

        
if __name__=="__main__":
    updateStockCode()
